﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace TPCN.DAL
{
    public class HangDAL
    {
        public static HangObj GetByID(string HangID)
        {
            HangObj obj = new HangObj();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbHang_GetByID";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@HangID", HangID);
                using (SqlDataReader dr = Database.db.GetDataToDataReader(cmd))
                {
                    if (dr.HasRows)
                    {
                        dr.Read();
                        obj = obj.SetValuesFromDataReader(dr);
                    }
                    dr.Close();
                }
            }
            return obj;
        }

        public static string GetNameByID(string HangID)
        {
            string result = String.Empty;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbHang_GetNameByID";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@HangID", HangID);
                using (SqlDataReader dr = Database.db.GetDataToDataReader(cmd))
                {
                    if (dr.HasRows)
                    {
                        dr.Read();
                        result = dr[0].ToString();
                    }
                    dr.Close();
                }
            }
            return result;
        }

        public static List<HangObj> GetByAll()
        {
            List<HangObj> result = new List<HangObj>();
            HangObj obj = new HangObj();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbHang_GetByAll";
                cmd.CommandType = CommandType.StoredProcedure;
                using (SqlDataReader dr = Database.db.GetDataToDataReader(cmd))
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            result.Add(obj.SetValuesFromDataReader(dr));
                        }
                    }
                    dr.Close();
                    obj = null;
                }
            }
            return result;
        }

        public static List<HangObj> GetByAllAdmin()
        {
            List<HangObj> result = new List<HangObj>();
            HangObj obj = new HangObj();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbHang_GetByAllAdmin";
                cmd.CommandType = CommandType.StoredProcedure;
                using (SqlDataReader dr = Database.db.GetDataToDataReader(cmd))
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            result.Add(obj.SetValuesFromDataReader(dr));
                        }
                    }
                    dr.Close();
                    obj = null;
                }
            }
            return result;
        }

        public static List<HangObj> GetByBestSeller(int top)
        {
            List<HangObj> result = new List<HangObj>();
            HangObj obj = new HangObj();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbHang_GetByBestSeller";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@top", top);
                using (SqlDataReader dr = Database.db.GetDataToDataReader(cmd))
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            result.Add(obj.SetValuesFromDataReader(dr));
                        }
                    }
                    dr.Close();
                    obj = null;
                }
            }
            return result;
        }

        public static List<HangObj> GetByCategoryID(string categoryID)
        {
            List<HangObj> result = new List<HangObj>();
            HangObj obj = new HangObj();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbHang_GetByCategoryID";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@DanhmucID", categoryID);
                using (SqlDataReader dr = Database.db.GetDataToDataReader(cmd))
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            result.Add(obj.SetValuesFromDataReader(dr));
                        }
                    }
                    dr.Close();
                    obj = null;
                }
            }
            return result;
        }

        public static List<HangObj> GetByQuery(string query)
        {
            List<HangObj> result = new List<HangObj>();
            HangObj obj = new HangObj();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbHang_GetByQuery";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@query", query);
                using (SqlDataReader dr = Database.db.GetDataToDataReader(cmd))
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            result.Add(obj.SetValuesFromDataReader(dr));
                        }
                    }
                    dr.Close();
                    obj = null;
                }
            }
            return result;
        }

        public static int Insert(HangObj obj)
        {
            int id = 0;
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "sp_tbHang_Insert";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Tenhang", (obj.Tenhang == null) ? String.Empty : obj.Tenhang);
                    cmd.Parameters.AddWithValue("@Thongtinsoluoc", (obj.Thongtinsoluoc == null) ? String.Empty : obj.Thongtinsoluoc);
                    cmd.Parameters.AddWithValue("@Thongtinchitiet", (obj.Thongtinchitiet == null) ? String.Empty : obj.Thongtinchitiet);
                    cmd.Parameters.AddWithValue("@Xuatxu", (obj.Xuatxu == null) ? String.Empty : obj.Xuatxu);
                    cmd.Parameters.AddWithValue("@DonvitinhID", (obj.DonvitinhID == null) ? String.Empty : obj.DonvitinhID);
                    cmd.Parameters.AddWithValue("@Thutu", (obj.Thutu == null) ? "0" : obj.Thutu);
                    cmd.Parameters.AddWithValue("@Title", (obj.Title == null) ? String.Empty : obj.Title);
                    cmd.Parameters.AddWithValue("@Keyword", (obj.Keyword == null) ? String.Empty : obj.Keyword);
                    cmd.Parameters.AddWithValue("@Description", (obj.Description == null) ? String.Empty : obj.Description);
                    cmd.Parameters.AddWithValue("@Url", (obj.Url == null) ? String.Empty : obj.Url);
                    cmd.Parameters.AddWithValue("@Active", (obj.Active == null) ? String.Empty : obj.Active);
                    cmd.Parameters.AddWithValue("@Anh", (obj.Anh == null) ? String.Empty : obj.Anh);
                    cmd.Parameters.AddWithValue("@Solanmua", (obj.Solanmua == null) ? "0" : obj.Solanmua);
                    cmd.Parameters.AddWithValue("@Giathitruong", (obj.Giathitruong == null) ? "0" : obj.Giathitruong);
                    cmd.Parameters.AddWithValue("@Giabanle", (obj.Giabanle == null) ? "0" : obj.Giabanle);
                    cmd.Parameters.AddWithValue("@Giabanbuon", (obj.Giabanbuon == null) ? "0" : obj.Giabanbuon);

                    SqlParameter p = new SqlParameter("@ID", DbType.Int32);
                    p.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(p);

                    Database.db.ExecuteNonQuery(cmd);

                    id = Int32.Parse(cmd.Parameters["@ID"].Value.ToString());
                }
                return id;
            }
            catch (Exception)
            {
                return id;
            }
        }

        public static bool Update(HangObj obj)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "sp_tbHang_Update";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@HangID", (obj.HangID == null) ? String.Empty : obj.HangID);
                    cmd.Parameters.AddWithValue("@Tenhang", (obj.Tenhang == null) ? String.Empty : obj.Tenhang);
                    cmd.Parameters.AddWithValue("@Thongtinsoluoc", (obj.Thongtinsoluoc == null) ? String.Empty : obj.Thongtinsoluoc);
                    cmd.Parameters.AddWithValue("@Thongtinchitiet", (obj.Thongtinchitiet == null) ? String.Empty : obj.Thongtinchitiet);
                    cmd.Parameters.AddWithValue("@Xuatxu", (obj.Xuatxu == null) ? String.Empty : obj.Xuatxu);
                    cmd.Parameters.AddWithValue("@DonvitinhID", (obj.DonvitinhID == null) ? String.Empty : obj.DonvitinhID);
                    cmd.Parameters.AddWithValue("@Thutu", (obj.Thutu == null) ? "0" : obj.Thutu);
                    cmd.Parameters.AddWithValue("@Title", (obj.Title == null) ? String.Empty : obj.Title);
                    cmd.Parameters.AddWithValue("@Keyword", (obj.Keyword == null) ? String.Empty : obj.Keyword);
                    cmd.Parameters.AddWithValue("@Description", (obj.Description == null) ? String.Empty : obj.Description);
                    cmd.Parameters.AddWithValue("@Url", (obj.Url == null) ? String.Empty : obj.Url);
                    cmd.Parameters.AddWithValue("@Active", (obj.Active == null) ? String.Empty : obj.Active);
                    cmd.Parameters.AddWithValue("@Anh", (obj.Anh == null) ? String.Empty : obj.Anh);
                    cmd.Parameters.AddWithValue("@Solanmua", (obj.Solanmua == null) ? "0" : obj.Solanmua);
                    Database.db.ExecuteNonQuery(cmd);
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool UpdateWithPrice(HangObj obj)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "sp_tbHang_UpdateWithPrice";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@HangID", (obj.HangID == null) ? String.Empty : obj.HangID);
                    cmd.Parameters.AddWithValue("@Tenhang", (obj.Tenhang == null) ? String.Empty : obj.Tenhang);
                    cmd.Parameters.AddWithValue("@Thongtinsoluoc", (obj.Thongtinsoluoc == null) ? String.Empty : obj.Thongtinsoluoc);
                    cmd.Parameters.AddWithValue("@Thongtinchitiet", (obj.Thongtinchitiet == null) ? String.Empty : obj.Thongtinchitiet);
                    cmd.Parameters.AddWithValue("@Xuatxu", (obj.Xuatxu == null) ? String.Empty : obj.Xuatxu);
                    cmd.Parameters.AddWithValue("@DonvitinhID", (obj.DonvitinhID == null) ? String.Empty : obj.DonvitinhID);
                    cmd.Parameters.AddWithValue("@Thutu", (obj.Thutu == null) ? "0" : obj.Thutu);
                    cmd.Parameters.AddWithValue("@Title", (obj.Title == null) ? String.Empty : obj.Title);
                    cmd.Parameters.AddWithValue("@Keyword", (obj.Keyword == null) ? String.Empty : obj.Keyword);
                    cmd.Parameters.AddWithValue("@Description", (obj.Description == null) ? String.Empty : obj.Description);
                    cmd.Parameters.AddWithValue("@Url", (obj.Url == null) ? String.Empty : obj.Url);
                    cmd.Parameters.AddWithValue("@Active", (obj.Active == null) ? String.Empty : obj.Active);
                    cmd.Parameters.AddWithValue("@Anh", (obj.Anh == null) ? String.Empty : obj.Anh);
                    cmd.Parameters.AddWithValue("@Solanmua", (obj.Solanmua == null) ? "0" : obj.Solanmua);
                    cmd.Parameters.AddWithValue("@Giabanbuon", (obj.Giabanbuon == null) ? "0" : obj.Giabanbuon);
                    cmd.Parameters.AddWithValue("@Giabanle", (obj.Giabanle == null) ? "0" : obj.Giabanle);
                    cmd.Parameters.AddWithValue("@Giathitruong", (obj.Giathitruong == null) ? "0" : obj.Giathitruong);
                    Database.db.ExecuteNonQuery(cmd);
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool UpdateCount(string HangID, int quantity)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "sp_tbHang_UpdateCount";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@HangID", (HangID == null) ? String.Empty : HangID);
                    cmd.Parameters.AddWithValue("@Soluong", quantity);
                    Database.db.ExecuteNonQuery(cmd);
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool Delete(string HangID)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_tbHang_DeleteByID";
                    cmd.Parameters.AddWithValue("@HangID", HangID);
                    Database.db.ExecuteNonQuery(cmd);
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}
